class CreateGroupingAnalytics < ActiveRecord::Migration
  def self.up
    create_table :grouping_analytics do |t|
      t.string :name
      t.string :search_term
      t.text :sql

      t.timestamps
    end
    
    # add a few (put limit 1, which will be replaced)
    GroupingAnalytic.create(:name => "Top Tags", :search_term => "content", 
      :sql => "select tags.id as id, tags.name as name, count(*) as num from reports, tags, taggings 
        where reports.id = taggings.report_id and taggings.tag_id=tags.id and (tags.is_hash is null or tags.is_hash=0)
        group by tags.id order by num desc limit 1")
    GroupingAnalytic.create(:name => "Top Hashtags", :search_term => "content",
      :sql => "select tags.id as id, tags.name as name, count(*) as num from reports, tags, taggings 
      where reports.id = taggings.report_id and taggings.tag_id=tags.id and (tags.is_hash=1)
      group by tags.id order by num desc limit 1")
    GroupingAnalytic.create(:name => "Top Authors", :search_term => "authorname",
      :sql => "select authors.id as id, authors.name as name, count(*) as num from reports, authors 
      where reports.author_id = authors.id
      group by authors.id order by num desc limit 1")
    GroupingAnalytic.create(:name => "Top Urls", :search_term => "content",
      :sql => "select urls.id as id, urls.address as name, count(*) as num from reports, urls, url_refs
        where reports.id = url_refs.report_id and url_refs.url_id=urls.id
        group by urls.id order by num desc limit 1")
  end

  def self.down
    drop_table :grouping_analytics
  end
end
